Skip Headers

Oracle® Database Advanced Replication Management API Reference 10g Release 2 (10.2) Part Number B14227-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 12 DBMS_DEFER

`DBMS_DEFER` is the user interface to a replicated transactional deferred remote procedure call facility. Replicated applications use the calls in this interface to queue procedure calls for later transactional execution at remote nodes.

These procedures are typically called from either after row triggers or application specified update procedures.

This chapter contains this topic:

- Summary of DBMS_DEFER Subprograms

## Summary of DBMS_DEFER Subprograms

Table 12-1 DBMS_DEFER Package Subprograms

Subprogram | Description
--- | ---
"CALL Procedure" | Builds a deferred call to a remote procedure.
"COMMIT_WORK Procedure" | Performs a transaction commit after checking for well-formed deferred remote procedure calls.
"datatype_ARG Procedure" | Provides the data that is to be passed to a deferred remote procedure call.
"TRANSACTION Procedure" | Indicates the start of a new deferred transaction.

### CALL Procedure

This procedure builds a deferred call to a remote procedure.

Syntax

    DBMS_DEFER.CALL (
       schema_name       IN   VARCHAR2,
       package_name      IN   VARCHAR2,
       proc_name         IN   VARCHAR2,
       arg_count         IN   NATURAL,
       { nodes           IN   node_list_t
       | group_name      IN   VARCHAR2 :=''});

Note: This procedure is overloaded. The `nodes` and `group_name` parameters are mutually exclusive.

Parameters

Table 12-2 CALL Procedure Parameters

Parameter | Description
--- | ---
    schema_name
| Name of the schema in which the stored procedure is located.
    package_name
| Name of the package containing the stored procedure. The stored procedure must be part of a package. Deferred calls to standalone procedures are not supported.
    proc_name
| Name of the remote procedure to which you want to defer a call.
    arg_count
| Number of parameters for the procedure. You must have one call to `DBMS_DEFER`.datatype`_``ARG` for each of these parameters. Note: You must include all of the parameters for the procedure, even if some of the parameters have defaults.
    nodes
| A PL/SQL index-by table of fully qualified database names to which you want to propagate the deferred call. The table is indexed starting at position 1 and continuing until a `NULL` entry is found, or the `no_data_found` exception is raised. The data in the table is case insensitive. This parameter is optional.
    group_name
| Reserved for internal use.

Exceptions

Table 12-3 CALL Procedure Exceptions

Exception | Description
--- | ---
    ORA-23304 (malformedcall)
| Previous call was not correctly formed.
    ORA-23319
| Parameter value is not appropriate.
    ORA-23352
| Destination list (specified by `nodes` or by a previous `DBMS_DEFER`.`TRANSACTION` call) contains duplicates.

### COMMIT_WORK Procedure

This procedure performs a transaction commit after checking for well-formed deferred remote procedure calls.

Syntax

    DBMS_DEFER.COMMIT_WORK (
       commit_work_comment IN VARCHAR2);

Parameters

Table 12-4 COMMIT_WORK Procedure Parameters

Parameter | Description
--- | ---
    commit_work_comment
| Equivalent to the `COMMIT` `COMMENT` statement in SQL.

Exceptions

Table 12-5 COMMIT_WORK Procedure Exceptions

Exception | Description
--- | ---
    ORA-23304 (malformedcall)
| Transaction was not correctly formed or terminated.

### datatype_ARG Procedure

This procedure provides the data that is to be passed to a deferred remote procedure call. Depending upon the type of the data that you need to pass to a procedure, you must call one of the following procedures for each argument to the procedure.

You must specify each parameter in your procedure using the datatype`_``ARG` procedure after you execute `DBMS_DEFER.CALL`. That is, you cannot use the default parameters for the deferred remote procedure call. For example, suppose you have the following procedure:

    CREATE OR REPLACE PACKAGE my_pack AS
       PROCEDURE my_proc(a VARCHAR2, b VARCHAR2 DEFAULT 'SALES');
    END;
    /

When you run the `DBMS_DEFER.CALL` procedure, you must include a separate procedure call for each parameter in the `my_proc` procedure:

    CREATE OR REPLACE PROCEDURE load_def_tx IS
       node DBMS_DEFER.NODE_LIST_T;
    BEGIN
       node(1) := 'MYCOMPUTER.WORLD';
       node(2) := NULL;
       DBMS_DEFER.TRANSACTION(node);
       DBMS_DEFER.CALL('PR', 'MY_PACK', 'MY_PROC', 2);
       DBMS_DEFER.VARCHAR2_ARG('TEST');
       DBMS_DEFER.VARCHAR2_ARG('SALES'); -- required, cannot omit to use default
    END;
    /

Note: The `ANYDATA_ARG` procedure supports the following user-defined types: object types, collections, and `REF`s. See Oracle Database SQL Reference and Oracle Database Application Developer's Guide - Object-Relational Features for more information about the `ANYDATA` datatype. This procedure uses abbreviations for some datetime and interval datatypes. For example, `TSTZ` is used for the `TIMESTAMP` `WITH` `TIME` `ZONE` datatype. For information about these abbreviations, see "Abbreviations for Datetime and Interval Datatypes".

Syntax

    DBMS_DEFER.ANYDATA_ARG      (arg  IN ANYDATA);
    DBMS_DEFER.NUMBER_ARG       (arg  IN NUMBER);
    DBMS_DEFER.DATE_ARG         (arg  IN DATE);
    DBMS_DEFER.VARCHAR2_ARG     (arg  IN VARCHAR2);
    DBMS_DEFER.CHAR_ARG         (arg  IN CHAR);
    DBMS_DEFER.ROWID_ARG        (arg  IN ROWID);
    DBMS_DEFER.RAW_ARG          (arg  IN RAW);
    DBMS_DEFER.BLOB_ARG         (arg  IN BLOB);
    DBMS_DEFER.CLOB_ARG         (arg  IN CLOB);
    DBMS_DEFER.NCLOB_ARG        (arg  IN NCLOB);
    DBMS_DEFER.NCHAR_ARG        (arg  IN NCHAR);
    DBMS_DEFER.NVARCHAR2_ARG    (arg  IN NVARCHAR2);
    DBMS_DEFER.ANY_CLOB_ARG     (arg  IN CLOB);
    DBMS_DEFER.ANY_VARCHAR2_ARG (arg  IN VARCHAR2);
    DBMS_DEFER.ANY_CHAR_ARG     (arg  IN CHAR);
    DBMS_DEFER.IDS_ARG          (arg  IN DSINTERVAL_UNCONSTRAINED);
    DBMS_DEFER.IYM_ARG          (arg  IN YMINTERVAL_UNCONSTRAINED);
    DBMS_DEFER.TIMESTAMP_ARG    (arg  IN TIMESTAMP_UNCONSTRAINED);
    DBMS_DEFER.TSLTZ_ARG        (arg  IN TIMESTAMP_LTZ_UNCONSTRAINED);
    DBMS_DEFER.TSTZ_ARG         (arg  IN TIMESTAMP_TZ_UNCONSTRAINED);

Parameters

Table 12-6 datatype_ARG Procedure Parameters

Parameter | Description
--- | ---
    arg
| Value of the parameter that you want to pass to the remote procedure to which you previously deferred a call.

Exceptions

Table 12-7 datatype_ARG Procedure Exceptions

Exception | Description
--- | ---
    ORA-23323
| Argument value is too long.

### TRANSACTION Procedure

This procedure indicates the start of a new deferred transaction. If you omit this call, then Oracle considers your first call to `DBMS_DEFER`.`CALL` to be the start of a new transaction.

Syntax

    DBMS_DEFER.TRANSACTION (
       nodes  IN   node_list_t);

Note: This procedure is overloaded. The behavior of the version without an input parameter is similar to that of the version with an input parameter, except that the former uses the `nodes` in the `DEFDEFAULTDEST` view instead of using the nodes in the nodes parameter.

Parameters

Table 12-8 TRANSACTION Procedure Parameters

Parameter | Description
--- | ---
    nodes
| A PL/SQL index-by table of fully qualified database names to which you want to propagate the deferred calls of the transaction. The table is indexed starting at position 1 and continuing until a `NULL` entry is found, or the `no_data_found` exception is raised. The data in the table is case insensitive.

Exceptions

Table 12-9 TRANSACTION Procedure Exceptions

Exception | Description
--- | ---
    ORA-23304 (malformedcall)
| Previous transaction was not correctly formed or terminated.
    ORA-23319
| Parameter value is not appropriate.
    ORA-23352
| Raised by `DBMS_DEFER`.`CALL` if the node list contains duplicates.